package com.yunhe.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yunhe.pojo.PayOrder;
import com.yunhe.pojo.workonePojo;
import com.yunhe.pojo.worktwoPoji;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;


public interface PayOrderMapper  extends BaseMapper<PayOrder> {

    @Select("SELECT po.totalMoney totalMoney,po.totalCount totalCount,po.totalMoney/po.totalCount personAvgMoney\n" +
            "FROM \n" +
            "(SELECT sum(TOTAL_AMOUNT) totalMoney,COUNT(ID) totalCount\n" +
            "\t\t\tFROM pay_order \n" +
            "\t\t\tWHERE  TRADE_STATE = 2 AND(TO_DAYS(NOW( ) ) - TO_DAYS(UPDATE_TIME) <= 1 )\n" +
            "\t\t\tGROUP BY DATE_FORMAT(UPDATE_TIME,'%m-%d-%Y')  DESC) po")
    ArrayList<workonePojo> dayAndToday();

    @Select("SELECT \n" +
            "\t\tt1.DAY,\n" +
            "\t\tIFNULL(t2.totalMoney, 0) AS totalMoney\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\t@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY\n" +
            "\t\tFROM\n" +
            "\t\t\t(\n" +
            "\t\t\t\tSELECT\n" +
            "\t\t\t\t\t@cdate := DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'), INTERVAL + 1 DAY)\n" +
            "\t\t\t\tFROM\n" +
            "\t\t\t\t\tpay_order\n" +
            "\t\t\t) t0\n" +
            "\t\tLIMIT 15\n" +
            "\t) t1\n" +
            "LEFT JOIN (\n" +
            "\tSELECT sum(TOTAL_AMOUNT) totalMoney,DATE_FORMAT(UPDATE_TIME,'%Y-%m-%d') time\n" +
            "   FROM pay_order \n" +
            "   WHERE  TRADE_STATE = 2 AND(TO_DAYS(NOW( ) ) - TO_DAYS(UPDATE_TIME) <= 15 )\n" +
            "   GROUP BY DATE_FORMAT(UPDATE_TIME,'%Y-%m-%d')  DESC\n" +
            ") t2 \n" +
            "ON t1. DAY = t2. time\n" +
            "ORDER BY\n" +
            "\tDAY DESC\n")
    ArrayList<worktwoPoji> shiWuTian();

}
